package org.farec.tcc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.farec.tcc.modelo.Responsavel;

public class ResponsavelDAO {
	
	private Connection connection;
	
	public ResponsavelDAO(Connection connection)
	{
		this.connection=connection;
	}
	
	public void adiciona(Responsavel responsavel)
	{
		String sql = "INSERT INTO responsavel(nome) VALUES(?)";
		try
		{
			//Prepara o statement para insercao
			PreparedStatement stmt = connection.prepareStatement(sql);
			
			//seta os dados
			stmt.setString(1, responsavel.getNome());
						
			stmt.execute();
			stmt.close();
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}

	public List<Responsavel> getLista(String ordem)
	{
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("SELECT * from responsavel order by "+ordem);
			ResultSet rs = stmt.executeQuery();
			List<Responsavel> responsaveis = new ArrayList<Responsavel>();
			
			while(rs.next())
			{
				//Criando um nova instancia
				Responsavel responsavel = new Responsavel();
				responsavel.setCodigo(rs.getInt("id"));
				responsavel.setNome(rs.getString("nome"));
								
				//Adicionando objeto a lista
				responsaveis.add(responsavel);
			}
			
			rs.close();
			stmt.close();
			return responsaveis;
			
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}
	
	public void altera(Responsavel responsavel)
	{
		String sql = "UPDATE responsavel SET nome=? where id=?";
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, responsavel.getNome());
			stmt.setInt(2, responsavel.getCodigo());
			
			stmt.execute();
			stmt.close();
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}
	
	public Responsavel remove(Responsavel responsavel)
	{
		try
		{

			PreparedStatement stmt = this.connection.prepareStatement("SELECT responsavel from area where responsavel=?");
			stmt.setString(1, responsavel.getNome());
			
			ResultSet rs = stmt.executeQuery();
			
			if(!rs.next())
			{
				PreparedStatement stmt2 = this.connection.prepareStatement("DELETE from responsavel where id=? and nome=?");
				stmt2.setLong(1, responsavel.getCodigo());
				stmt2.setString(2, responsavel.getNome());
				stmt2.execute();
				stmt2.close();
				
				responsavel.setAlerta(false);
			}
			
			else
			{

				responsavel.setAlerta(true);
			}

		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		
		return responsavel;
	}
	
	public Responsavel buscaPorCodigo(int codigo)
	{
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("SELECT * from responsavel where id=?");
			
			stmt.setInt(1, codigo);
			
			ResultSet rs = stmt.executeQuery();
			Responsavel responsavel = new Responsavel();
			if(rs.next())
			{
				//Criando um nova instancia
				responsavel.setCodigo(rs.getInt("id"));
				responsavel.setNome(rs.getString("nome"));
			}
	
			rs.close();
			stmt.close();
			return responsavel;
			
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		
	}
	
	
	public Responsavel getResponsavel(Responsavel responsavel)
	{
		ResultSet rs;
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("select nome from responsavel where nome ILIKE ?");
			stmt.setString(1, responsavel.getNome());
			rs=stmt.executeQuery();
			
			if(rs.next())
			{
				responsavel.setAlerta(true);
			}
			
			else
			{
				responsavel.setAlerta(false);
			}
			stmt.close();
			rs.close();
		
		}
		
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		return responsavel;
		
	}
	
	public Responsavel getResponsavelUp(Responsavel responsavel)
	{
		ResultSet rs;
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("select nome from responsavel where nome ILIKE ? and id!=?");
			stmt.setString(1, responsavel.getNome());
			stmt.setInt(2, responsavel.getCodigo());
			rs=stmt.executeQuery();
			
			if(rs.next())
			{
				responsavel.setAlerta(true);
			}
			
			else
			{
				responsavel.setAlerta(false);
			}
			stmt.close();
			rs.close();
		
		}
		
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		return responsavel;
		
	}
		
}
